﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.IO;

namespace CheckDB
{
    public class AcctionCheckDAL
    {
        DataTable Table_Form = new DataTable();
        DataTable Table_To = new DataTable();
        DataTable Table_Column = new DataTable();
        DataTable Table_Column_From = new DataTable();
        DataTable Table_Column_To = new DataTable();
        DataTable Table_View_From = new DataTable();
        DataTable Table_View_To = new DataTable();
        List<string> LView = new List<string>();
        List<string> LTable = new List<string>();
        List<string> LColumn = new List<string>();
        List<string> LDTable = new List<string>();
        List<string> LDView = new List<string>();
        OleDbConnection Conn = new OleDbConnection();
        string Query;
        string newPath;
        List<string> resourceNames;

        public AcctionCheckDAL()
        {
            resourceNames = new List<string>(this.GetType().Assembly.GetManifestResourceNames());
            LTable = resourceNames.FindAll(
            delegate(string Name)
            {
                return Name.Contains("CheckDB.TextQuery.Vietkins.Table");
            }
            );
            LView = resourceNames.FindAll(
            delegate(string Name)
            {
                return Name.Contains("CheckDB.TextQuery.Vietkins.View");
            }
            );
        }

        public AcctionCheckDAL(bool b)
        {
            resourceNames = new List<string>(this.GetType().Assembly.GetManifestResourceNames());
            LTable = resourceNames.FindAll(
            delegate(string Name)
            {
                return Name.Contains("CheckDB.TextQuery.VietEs.Table");
            }
            );
            LView = resourceNames.FindAll(
            delegate(string Name)
            {
                return Name.Contains("CheckDB.TextQuery.VietEs.View");
            }
            );
        }

        public void GetTablesFrom()
        {
            try
            {
                string NameTable = "Table_From";
                Table_Form.TableName = NameTable;
                Conn = ClsConnection.OleDBConnect(ClsConnection.connectString1);
                Table_Form = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null,
            null, "Table"});
                Conn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public void GetTablesTo()
        {
            try
            {
                string NameTable = "Table_To";
                Table_To.TableName = NameTable;
                Conn = ClsConnection.OleDBConnect(ClsConnection.connectString2);
                Table_To = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null, null, 
            null, "Table"});
                Conn.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("GetTablesTo()\n" + e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void AddTable(string name)
        {
            try
            {
                OleDbCommand Cmm = new OleDbCommand();

                string fullname = LTable.Find(
                    delegate(string Name)
                    {
                        return Name.Contains(name + ".txt");
                    }
                );

                Query = GetFromResources(fullname);

                Conn = ClsConnection.OleDBConnect(ClsConnection.connectString2);
                Cmm.Connection = Conn;
                Cmm.CommandText = Query;
                try
                {
                    Cmm.ExecuteNonQuery();
                    //WriteFile(di, name, Query);
                    LDTable.Add("Tabel:  " + name);
                }
                catch (Exception e)
                {
                    MessageBox.Show("AddTable(string name), ExecuteNonQuery()\n" + e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                Conn.Close();
                Cmm.Connection.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("AddTable(string name)\n" + e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void AddColumn(string table_From, string table_To)
        {
            try
            {
                OleDbCommand Cmm = new OleDbCommand();
                List<string> NameColumn = new List<string>();
                List<string> DataTypeColumn = new List<string>();

                Conn = ClsConnection.OleDBConnect(ClsConnection.connectString2);
                Table_Column_To = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[]{null, null,
                table_To, null});
                Conn.Close();

                Query = GetItemTable(table_From + ".txt");
                Query = Query.Replace("(255)", "?");
                if (Query != null)
                {
                    string[] str = Query.Split(new Char[]{'(', ')'});
                    str = str[1].Split(new Char[]{','});
                    for(int i = 0; i < str.Length; i ++)
                    {
                        string[] ColName = str[i].Split(new Char[] { '[', ']' });
                        NameColumn.Add(ColName[1].Trim());
                        DataTypeColumn.Add(ColName[2].Trim());
                    }
                }
                //ALTER TABLE Employees ADD COLUMN Notes TEXT(25)
                for (int i = 0; i < NameColumn.Count; i ++ )
                {
                    bool temp = true;
                    foreach (DataRow rw_To in Table_Column_To.Rows)
                    {
                        if (NameColumn[i] == rw_To["COLUMN_NAME"].ToString())
                        {
                            temp = false;
                        }
                    }
                    if (temp)
                    {
                        Query = "ALTER TABLE [" + table_To + "] ADD COLUMN [" + NameColumn[i] + "] " + DataTypeColumn[i].Replace("?", "(255)");
                        Conn = ClsConnection.OleDBConnect(ClsConnection.connectString2);
                        Cmm.Connection = Conn;
                        Cmm.CommandText = Query;
                        try
                        {
                            Cmm.ExecuteNonQuery();
                            LColumn.Add("Column:  " + NameColumn[i] + "  (" + table_To + ")");
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("AddColumn(string table_From, string table_To), Cmm.ExecuteNonQuery();\n" + e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                        finally
                        {
                            Conn.Close();
                            Cmm.Connection.Close();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("AddColumn(string table_From, string table_To)\n" + e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void CheckAndAddTable()
        {
            if (LTable.Count > 0)
            {
                for (int i = 0; i < LTable.Count; i ++)//foreach (string rw_From in LTable)//
                {
                    bool temp = true;
                    string str1 = LTable[i].ToString();
                    string[] strTemp = str1.Split(new Char[] { '.' });
                    foreach (DataRow rw_To in Table_To.Rows)
                    {
                        if (strTemp[4] == rw_To["TABLE_NAME"].ToString())
                        {
                            temp = false;
                        }
                    }

                    if (temp)
                    {
                        //CreateFile(di, strTemp[4]);
                        AddTable(strTemp[4]);
                    }
                }
            }
        }

        public void CheckAndAddColumn()
        {
            if ((LTable.Count > 0) && (Table_To.Rows.Count > 0))
            {
                LTable.Sort();
                foreach (string rw_From in LTable)
                {
                    string[] strTemp = rw_From.Split(new Char[] { '.' });
                    foreach (DataRow rw_To in Table_To.Rows)
                    {
                        if (strTemp[4] == rw_To["TABLE_NAME"].ToString())
                        {
                            AddColumn(strTemp[4], rw_To["TABLE_NAME"].ToString());
                        }
                    }
                }
            }
        }

        public void CheckView(DirectoryInfo di)
        {
            Conn = ClsConnection.OleDBConnect(ClsConnection.connectString2);
            Table_View_To = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null, null,
                null, "VIEW"});
            Conn.Close();

            foreach (string rw_From in LView)
            {
                bool temp = true;
                foreach (DataRow rw_To in Table_View_To.Rows)
                {
                    if (rw_From == rw_To["TABLE_NAME"].ToString())
                    {
                        temp = false;
                    }
                }
                if (temp)
                {
                    //Đang làm add View
                    LDView.Add(rw_From);
                }
            }
        }

        public void DisplayView()
        {
            if (LView.Count > 0 || LTable.Count > 0 || LColumn.Count > 0)
            {
                frmView view = new frmView(LDView, LDTable, LColumn);
                view.ShowDialog();
            }
            else
                MessageBox.Show("Bạn đang sử dụng chương trình mới nhất của chúng tôi\n Vì vậy không cần cập nhật nưa!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        public void AddView(DirectoryInfo di)
        {
            try
            {
                FileInfo[] Files = di.GetFiles("*.txt");
                DateTime[] creationTimes = new DateTime[Files.Length];
                for (int i = 0; i < Files.Length; i++)
                {
                    creationTimes[i] = Files[i].CreationTime;
                }
                Array.Sort(creationTimes, Files);
                #region Insert Proc Sys
                //List<FileInfo> ListFile = new List<FileInfo>(Files);
                LView = new List<string>();
                LView.Add("D_linhvuc.txt");
                LView.Add("ThongkemamnonMau2.txt");
                LView.Add("ThongkemamnonMau2_2.txt");
                LView.Add("Vw_Tre_namtrehoc.txt");
                LView.Add("vwChitietloaiphong.txt");
                LView.Add("vwcsvc.txt");
                LView.Add("View_Xuathanghoa.txt");
                LView.Add("vwMucthuNamMaugiao.txt");
                LView.Add("vwMucthuThangMaugiao.txt");
                LView.Add("Thongkemamnon_csvc_daunam.txt");
                LView.Add("Thongkemamnon_csvc_daunam_2.txt");
                LView.Add("ThongkemamnonMau3.txt");
                LView.Add("ThongkemamnonMau3_2.txt");
                LView.Add("View_Nhaphanghoa.txt");
                LView.Add("vwMucthuThangNhatre.txt");
                foreach (string str in LView)
                {
                    foreach (FileInfo file in Files)
                    {
                        if (SearchFile(file, str))
                        {
                            string fName = file.Name.Substring(0, file.Name.Length - 4);
                            InsertProc(fName, di);
                        }
                    }
                }
                #endregion
                foreach (FileInfo fi in Files)
                {
                    string fName = fi.Name.Substring(0, fi.Name.Length - 4);
                    #region da co thi bo qua
                    switch (fName)
                    {
                        case "D_linhvuc":
                            continue;
                        case "ThongkemamnonMau2":
                            continue;
                        case "ThongkemamnonMau2_2":
                            continue;
                        case "Vw_Tre_namtrehoc":
                            continue;
                        case "vwChitietloaiphong":
                            continue;
                        case "vwcsvc":
                            continue;
                        case "View_Xuathanghoa":
                            continue;
                        case "vwMucthuNamMaugiao":
                            continue;
                        case "vwMucthuThangMaugiao":
                            continue;
                    }
                    #endregion
                    InsertProc(fName, di);
                }
                Conn.Close();
            }
            catch (Exception e)
            {
            }
        }

        public void AddView()
        {
            try
            {
                List<string> LTemp = new List<string>();
                #region Insert Proc Sys
                //List<FileInfo> ListFile = new List<FileInfo>(Files);
                LTemp.Add("D_linhvuc.txt");
                LTemp.Add("ThongkemamnonMau2.txt");
                LTemp.Add("ThongkemamnonMau2_2.txt");
                LTemp.Add("Vw_Tre_namtrehoc.txt");
                LTemp.Add("vwChitietloaiphong.txt");
                LTemp.Add("vwcsvc.txt");
                LTemp.Add("View_Xuathanghoa.txt");
                LTemp.Add("vwMucthuNamMaugiao.txt");
                LTemp.Add("vwMucthuThangMaugiao.txt");
                LTemp.Add("Thongkemamnon_csvc_daunam.txt");
                LTemp.Add("Thongkemamnon_csvc_daunam_2.txt");
                LTemp.Add("Thongkemamnon_csvc_daunam_3.txt");
                LTemp.Add("ThongkemamnonMau3.txt");
                LTemp.Add("ThongkemamnonMau3_2.txt");
                LTemp.Add("View_Nhaphanghoa.txt");
                LTemp.Add("vwMucthuNamNhatre.txt");
                LTemp.Add("vwMucthuThangNhatre.txt");
                LTemp.Add("vwTre.txt");
                foreach (string str in LTemp)
                {
                    string fName = str.Substring(0, str.Length - 4);
                    Query = GetItemView(str);
                    if (Query != null)
                    {
                        InsertProc(Query, fName);
                    }
                }
                #endregion

                LView.Sort();
                foreach (string str in LView)
                {
                    string[] strTemp = str.Split(new Char[] { '.' });
                    #region da co thi bo qua
                    switch (strTemp[4] + ".txt")
                    {
                        case "D_linhvuc.txt":
                            continue;
                        case "ThongkemamnonMau2.txt":
                            continue;
                        case "ThongkemamnonMau2_2.txt":
                            continue;
                        case "Vw_Tre_namtrehoc.txt":
                            continue;
                        case "vwChitietloaiphong.txt":
                            continue;
                        case "vwcsvc.txt":
                            continue;
                        case "View_Xuathanghoa.txt":
                            continue;
                        case "vwMucthuNamMaugiao.txt":
                            continue;
                        case "vwMucthuThangMaugiao.txt":
                            continue;
                        case "Thongkemamnon_csvc_daunam.txt":
                            continue;
                        case "Thongkemamnon_csvc_daunam_2.txt":
                            continue;
                        case "ThongkemamnonMau3.txt":
                            continue;
                        case "ThongkemamnonMau3_2.txt":
                            continue;
                        case "View_Nhaphanghoa.txt":
                            continue;
                        case "vwMucthuNamNhatre.txt":
                            continue;
                        case "vwMucthuThangNhatre.txt":
                            continue;
                    }
                    #endregion
                    Query = GetItemView(str);
                    if (Query != null)
                    {
                        InsertProc(Query, strTemp[4]);
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("AddView()\n" + e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void InsertProc(string Query, string fName)
        {
            OleDbCommand cm = new OleDbCommand();
            cm.Connection = Conn = ClsConnection.OleDBConnect(ClsConnection.connectString2);
            cm.CommandText = "DROP VIEW [" + fName + "]";
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }
            cm.CommandText = "CREATE PROCEDURE [" + fName + "] AS " + Query;
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show("InsertProc(string Query, string fName),cm.ExecuteNonQuery()\n" + e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            cm.Connection.Close();
        }

        public string GetItemView(string _Name)
        {
            string str = null;
            bool b = LView.Exists(
            delegate(string Name)
            {
                return Name.Contains(_Name);
            }
            );
            if(b)
            {
                str = LView.Find(
                    delegate(string Name)
                    {
                        return Name.Contains(_Name);
                    }
                );
                str = GetFromResources(str);
            }
            return str;
        }

        public string GetItemTable(string _Name)
        {
            string str = null;
            bool b = LTable.Exists(
            delegate(string Name)
            {
                return Name.Contains(_Name);
            }
            );
            if (b)
            {
                str = LTable.Find(
                    delegate(string Name)
                    {
                        return Name.Contains(_Name);
                    }
                );
                str = GetFromResources(str);
            }
            return str;
        }

        public static bool SearchFile(FileInfo file, string name)
        {
            if (file.Name == name)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void InsertProc(string fName, DirectoryInfo di)
        {
            OleDbCommand cm = new OleDbCommand();
            cm.Connection = Conn = ClsConnection.OleDBConnect(ClsConnection.connectString2);
            cm.CommandText = "DROP VIEW [" + fName + "]";
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }
            Query = File.ReadAllText(@"" + di.FullName + fName + ".txt");
            cm.CommandText = "CREATE PROCEDURE [" + fName + "] AS " + Query;
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception)
            {

            }
        }

        public void CreateFile(DirectoryInfo di, string name)
        {
            try
            {
                //string path = @"" + di.FullName + "\\VietEs\\Table\\";
                string path = @"" + di.FullName + "\\VietEs\\View\\";
                newPath = Path.Combine(path, name + ".txt");
                File.Create(newPath).Close();
            }
            catch
            {
            }
        }

        public void WriteFile(DirectoryInfo di, string name, string text)
        {
            try
            {
                System.IO.File.WriteAllText(@"" + di.FullName + "\\VietEs\\Table\\" + name + ".txt", text);
            }
            catch
            { }
        }

        internal string GetFromResources(string resourceName)
        {
            System.Reflection.Assembly assem = this.GetType().Assembly;
            using (Stream stream = assem.GetManifestResourceStream(resourceName))
            {
                using (var reader = new StreamReader(stream))
                {
                    return reader.ReadToEnd();
                }
            }
        }
    }
}
